package LawsonTransformJava.dao;
// Access concrete DAO implementation
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;


public class DAOAccessDB extends DAODB {
	protected Properties prop = null;
	protected String propFile = "/resources/msaconnect.properties";
	// @ TODO CLIENTSPECIFIC - Modify Directory structure for Access Database
	protected String xRefDB = "LawsonConversionV01.accdb";
	protected String dataDB = "LawsonConversionV01.accdb";
	protected String resource_base = null;
	protected String db_loc = null;
	protected String jdbcDriver = null;
	protected String dbDriver = null;
	protected String schema = null;
	protected String driverName = null;
  
	//Data input variables
	protected String urlMSAData = null;
	protected Statement stmtMSAData = null;
	protected Connection connMSAData = null;
	protected Statement countStmtMSAData = null;

	//Cross Reference variables
	protected String urlMSAXref = null;
	protected Statement stmtMSAXref = null;
	protected Connection connMSAXref = null;

	public DAOAccessDB(){
		try {
			setMSAProperties();
		    Class.forName(jdbcDriver);
		    //Update Cross Reference variables
//		    urlMSAXref = dbDriver.trim() + ";DBQ=" + xRefDB.trim() + ";DriverID=22;READONLY=true";
		    urlMSAXref = dbDriver.trim() + ";DBQ=" + db_loc + xRefDB.trim() + ";DriverID=22;READONLY=true";
		    connMSAXref = DriverManager.getConnection(urlMSAXref, "", "");
		    stmtMSAXref = connMSAXref.createStatement();	    

		    //Update Data variables
//		    urlMSAData = dbDriver.trim() + ";DBQ=" + dataDB.trim() + ";DriverID=22;READONLY=true";
		    urlMSAData = dbDriver.trim() + ";DBQ=" + db_loc + dataDB.trim() + ";DriverID=22;READONLY=true";
		    connMSAData = DriverManager.getConnection(urlMSAData, "", "");
		    stmtMSAData = connMSAData.createStatement();	    
//		    countStmtMSAData = connMSAData.createStatement();
		
		} catch (ClassNotFoundException e) {
		    // Could not find the database driver
			System.out.println("ClassNotFoundException: " + e);
		} catch (SQLException e) {
		    // Could not connect to the database
			System.out.println("SQLException: " + e);
		} catch (IOException e) {
			// Could no write properties file
			System.out.println("IOException: " + e);
		}
	}
	
	  // method to create Access connections
	  public Connection getDataConnection() {
	    // Use DRIVER and DBURL to create a connection
	    // Recommend connection pool implementation/usage
		
		try{
			if (connMSAData == null)
				return connMSAData = DriverManager.getConnection(urlMSAData, "", "");
		} catch (SQLException e) {
		    // Could not connect to the database
			System.out.println("SQLException: " + e);
		}
		  return connMSAData;
	  }

	  public Statement getDataStatement() {
		  try {
			  if (stmtMSAData == null)
				  return stmtMSAData = getDataConnection().createStatement();
		  } catch (SQLException e) {
			  // Could not connect to the database
			  System.out.println("SQLException: " + e);
		  }
		  return stmtMSAData;
	  }
	  
	  public String getSchema() {
		  if (!schema.equalsIgnoreCase(""))
			  return schema + ".";
		  return "";
	  }

	  private void setMSAProperties() throws IOException, ClassNotFoundException {
		    //Get the Base String for the Resource File
		    resource_base = System.getenv("RESOURCE_BASE");
		    prop = new Properties();
		    prop.load(new FileInputStream(resource_base + "/" + propFile));
		    
		    // Create a connection to the database
		    db_loc = prop.getProperty("db_loc");
		    jdbcDriver = prop.getProperty("jdbcdriver");
		    dbDriver = prop.getProperty("databasedriver");
		    schema = prop.getProperty("schema");
		    
		    if(System.getenv("DEBUG") != null && System.getenv("DEBUG").equalsIgnoreCase("TRUE")) {
			    System.out.println("db_loc:" + db_loc);
			    System.out.println("jdbcDriver:" + jdbcDriver);
			    System.out.println("dbDriver:" + dbDriver);
			    System.out.println("schema:" + schema);
		    }
	  }
	  
	  public void close() {
		  try {
			  connMSAXref.close();
			  stmtMSAXref.close();
			  connMSAData.close();
			  stmtMSAData.close();
		  } catch (SQLException sqle) {
			  System.out.println("SQLException - " + sqle);
		  }
	  }

	  public Statement getCountStatement() {
		  try {
			  if (countStmtMSAData == null)
				  return countStmtMSAData = getDataConnection().createStatement();
		  } catch (SQLException e) {
			  // Could not connect to the database
			  System.out.println("SQLException: " + e);
		  }
		  return countStmtMSAData;
	  }	  
}
